import numpy as np
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt
import os


DB = sqlite3.connect('2way3way.db')

# write my 3way table
table = pd.read_sql_query('select * from way3_70', con = DB)
table = table.loc[(table!='N').all(axis=1)]
table = table.applymap(lambda x: 70-ord(x))
table.to_sql('my3way', con = DB, index=False,if_exists = 'replace')

# write my 2way table
table = pd.read_sql_query('select * from way2_70', con = DB)
table = table.loc[(table!='N').all(axis=1)]
table = table.applymap(lambda x: 70-ord(x))
table.to_sql('my2way', con = DB, index=False,if_exists = 'replace')
DB.close()


# table2_array is a numpy array that maps input to output. The i-th element in table2_array is the output of the inputs s.t. i = 5*(input1-1)+(input2-1)
# table3_array is a numpy array that maps input to output. The i-th element in table3_array is the output of the inputs s.t. i = 25*(input_1-1) + 5*(input2-1)+(input3-1)

DB = sqlite3.connect('2way3way.db')
table3 = pd.read_sql_query('select * from my3way', DB)
table2 = pd.read_sql_query('select * from my2way', DB)
# Construct table indexed by single integer
table2_array = np.empty(shape=(25), dtype=int)
table2_array[(table2['Input 1']-1)*5 + table2['Input 2']-1] = table2['Output']
table3_array = np.empty(shape=(125), dtype=int)
table3_array[(table3['Input 1']-1)*25 + (table3['Input 2']-1)*5 + (table3['Input 3']-1)] = table3['Output']
np.save('table2_array.npy', table2_array)
np.save('table3_array.npy', table3_array)
DB.close()
